package com.penta.services;


import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.penta.entity.HeadInfoVO;
import com.penta.helper.CellStyleBuilder;
import com.penta.helper.FontBuilder;
import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFPicture;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 模板2输出服务类，主要用于将内容写入模板文档
 */
public class ModuleTwoService extends ModuleService {

    /**
     * writer1的当前索引
     */
    private int writer1CurrIndex=11;

    /**
     * writer2的当前索引
     */
    private int writer2CurrIndex=10;

    public ModuleTwoService(ExcelWriter writer1, ExcelWriter writer2, List<List<Object>> dataList, List<byte[]> pictureDataList) {
        this.writer1 = writer1;
        this.writer2 = writer2;
        this.dataList = dataList;
        this.pictureDataList = pictureDataList;
        this.writer1Eval = writer1.getWorkbook().getCreationHelper().createFormulaEvaluator();    // 计算句柄
        this.writer2Eval = writer2.getWorkbook().getCreationHelper().createFormulaEvaluator();    // 计算句柄
    }

    @Override
    public void execOutput() {

        /**
         * 取出表头信息
         */
        HeadInfoVO headInfoVO = getHeadInfoByDataList();
        outputHead(headInfoVO);      // 转出表头

        List<List<Object>> materialList = getMaterialListFromDataList(dataList);

        // 输出生成1的物料列表信息
        writeMaterialListForOutput1(materialList,headInfoVO);

        // 输出生成2的物料列表信息
        writeMaterialListForOutput2(materialList,headInfoVO);

    }

    /**
     * 输出生成1
     * @param materialList
     * @param headInfoVO
     */
    private void writeMaterialListForOutput1(List<List<Object>> materialList, HeadInfoVO headInfoVO) {

        /**
         * 如果物料列表为空，则不再写入，直接返回
         */
        if(CollectionUtil.isEmpty(materialList)) {
            return;
        }

        writer1.passRows(writer1CurrIndex);     // 先跳过一定的行，再进行写入

        int startIndex = writer1CurrIndex;      // 开始的索引

        /**
         * 遍历输出产品列表
         */
        for (List<Object> objects : materialList) {

            String no = ObjectUtil.isNotEmpty(objects.get(0))?String.valueOf(objects.get(0)):null;                // 序号
            String itemNo = ObjectUtil.isNotEmpty(objects.get(1))?String.valueOf(objects.get(1)):null;            // itemNo
            String description = ObjectUtil.isNotEmpty(objects.get(2))?String.valueOf(objects.get(2)):null;            // 描述，可能带逗号，我们只需要取逗号前的内容
            if(StrUtil.isNotBlank(description)) {
                description = description.split("[,|，]")[0];    // 截取逗号前的内容
            }
            String color = ObjectUtil.isNotEmpty(objects.get(3))?String.valueOf(objects.get(3)):null;            // 颜色
            String material = ObjectUtil.isNotEmpty(objects.get(4))?String.valueOf(objects.get(4)):null;      // material
            String itemSize = ObjectUtil.isNotEmpty(objects.get(5))?String.valueOf(objects.get(5)):null;      // itemSize
            int totalQty = 0;    // 总数量
            if(ObjectUtil.isNotEmpty(objects.get(6))) {
                try {
                    Integer.valueOf(String.valueOf(objects.get(6)));
                } catch (Exception e) {
                    //e.printStackTrace();
                }
            }
            double unitPrice = 0d;
            if(ObjectUtil.isNotEmpty(objects.get(7))) {
                try {
                    unitPrice = Double.valueOf(String.valueOf(objects.get(7)));     // 单价
                } catch (Exception e) {
                    //e.printStackTrace();
                }
            }

            String amountFormula = "G" + (writer1CurrIndex+1) + "*H" + (writer1CurrIndex+1) ;     // 总价的公式

            List<Object> rowData = CollUtil.newArrayList(no, itemNo, description, color,material,itemSize,totalQty,unitPrice,amountFormula);
            writeRowDataForOutput1(rowData,writer1CurrIndex);
            writer1CurrIndex++;

        }

        int endIndex = writer1CurrIndex;      // 开始的索引

        String ttlQtyFormula = "SUM(G" + (startIndex+1) + ":G" + endIndex + ")";
        String ttlAmountFormula = "SUM(I" + (startIndex+1) + ":I" + endIndex + ")";

        /**
         * 输出生成1，的汇总信息
         */
        writeTotalForOutput1(writer1,headInfoVO.getType(),ttlQtyFormula,ttlAmountFormula);
        writer1CurrIndex++;

        /**
         * 从这里开始，输出底部的信息
         * 底部也分为三部分
         */
        // (1) 输出 总金额与税收  TOTAL INVOICE FOB USD
        String collectAmountFormula = "I" + writer1CurrIndex;
        writeAmountAndInsuranceForOutput1(collectAmountFormula);

        // (2) 输出 总量、总重量信息
        String ttlCtnsFormula = "G" + (endIndex + 1);
        writeTtlCbmForOutput1(ttlCtnsFormula);

        // (3) 输出签名信息
        writeSignatureForOutput1();
    }

    /**
     * 输出签名内容
     */
    private void writeSignatureForOutput1() {

        /**
         * 跳两行空行
         */
        writer1.passCurrentRow();
        writer1CurrIndex++;
        writer1.passCurrentRow();
        writer1CurrIndex++;

        writer1.merge(writer1CurrIndex,writer1CurrIndex,0,8,"For and on behalf of",false);
        List<String> beHalfRowData = CollUtil.newArrayList("For and on behalf of","","","","","","","","");
        writer1.passCurrentRow();

        Row beHalfRowDataRow = writer1.getSheet().getRow(writer1CurrIndex);
        Font beHalfFont = new FontBuilder(writer1).setFontName("Arial").setFontHeight(9).setBold(false).build();
        for(int i=0;i<beHalfRowData.size();i++) {
            Cell currCell  = beHalfRowDataRow.getCell(i);
            if(currCell == null) {
                currCell = beHalfRowDataRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,1,1,1).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setDataFormat(2).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(beHalfFont).build());    // 只有左边框
        }
        writer1CurrIndex++;


        writer1.merge(writer1CurrIndex,writer1CurrIndex,0,8,"PENTA ASIA LIMITED",false);
        List<String> limitedRowData = CollUtil.newArrayList("PENTA ASIA LIMITED","","","","","","","","");
        writer1.passCurrentRow();
        //writer1.writeRow(limitedRowData);

        Row limitedRowDataRow = writer1.getSheet().getRow(writer1CurrIndex);
        Font limitedFont = new FontBuilder(writer1).setFontName("Arial").setFontHeight(18).setItalic(true).setBold(true).build();
        for(int i=0;i<limitedRowData.size();i++) {
            Cell currCell  = limitedRowDataRow.getCell(i);
            if(currCell == null) {
                currCell = limitedRowDataRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,1,1,1).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setDataFormat(2).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(limitedFont).build());    // 只有左边框
        }
        writer1CurrIndex++;

        // 下边框为中边框，
        writer1.merge(writer1CurrIndex,writer1CurrIndex,0,2,"",false);
        writer1.passCurrentRow();
        Row underLineRow = writer1.getSheet().getRow(writer1CurrIndex);
        if(underLineRow==null) {
            underLineRow = writer1.getSheet().createRow(writer1CurrIndex);
        }
        underLineRow.setHeightInPoints(10);
        for(int i=0;i<3;i++) {
            Cell currCell = underLineRow.getCell(i);
            if(currCell==null) {
                currCell = underLineRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorderForModule2Limited().setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setDataFormat(2).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(limitedFont).build());    // 只有左边框

        }
        writer1CurrIndex++;


        writer1.merge(writer1CurrIndex,writer1CurrIndex,0,8,"Authorized Signature(s)",false);
        writer1.passCurrentRow();

        Row signatureRow = writer1.getSheet().getRow(writer1CurrIndex);
        for(int i=0;i<beHalfRowData.size();i++) {
            Cell currCell  = signatureRow.getCell(i);
            if(currCell == null) {
                currCell = signatureRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,1,1,1).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setDataFormat(2).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(beHalfFont).build());    // 只有左边框
        }
        writer1CurrIndex++;

    }

    private void writeTtlCbmForOutput1(String ttlCtnsFormula) {

        // 定义字体
        Font font = new FontBuilder(writer1).setFontName("Arial").setFontHeight(9).setBold(true).build();

        /**
         * 先输出两行空行
         */
        writer1.merge(writer1CurrIndex,writer1CurrIndex,0,8,"SAY TOTAL US DOLLARS",false);
        List<String> sayTotalRowData = CollUtil.newArrayList("SAY TOTAL US DOLLARS","","","","","","","","");
        // writer1.writeRow(sayTotalRowData);
        writer1.passCurrentRow();
        Row sayTotalRowDataRow = writer1.getSheet().getRow(writer1CurrIndex);
        for(int i=0;i<sayTotalRowData.size();i++) {
            Cell currCell  = sayTotalRowDataRow.getCell(i);
            if(currCell == null) {
                currCell = sayTotalRowDataRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,1,1,1).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setDataFormat(2).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 只有左边框
        }
        writer1CurrIndex++;

        List<String> blankRowData = CollUtil.newArrayList("","","","","","","","","");
        writerBlankRow(writer1,blankRowData,writer1CurrIndex,"0");
        writer1CurrIndex++;


        /**
         * 分别输出CBM、CTNS、GW
         */
        //（1）输出CBM
        writer1.merge(writer1CurrIndex,writer1CurrIndex,0,1,"TOTAL CBM:",false);
        List<Object> cbmData = CollUtil.newArrayList("TOTAL CBM:",null,null,null,null,null,null,null,null);
        writer1.passCurrentRow();
        Row cbmDataRow = writer1.getSheet().getRow(writer1CurrIndex);
        for(int i=0;i<cbmData.size();i++) {
            Cell currCell  = cbmDataRow.getCell(i);
            if(currCell == null) {
                currCell = cbmDataRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,1,1,1).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setDataFormat(2).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 只有左边框

        }
        writer1CurrIndex++;

        //(2)，输出CTNS
        writer1.merge(writer1CurrIndex,writer1CurrIndex,0,1,"TOTAL CTNS:",false);
        List<Object> ctnsData = CollUtil.newArrayList("TOTAL CTNS:",null,null,null,null,null,null,null,null);
        writer1.passCurrentRow();
        Row ctnsDataRow = writer1.getSheet().getRow(writer1CurrIndex);

        for(int i=0;i<ctnsData.size();i++) {
            Cell currCell  = ctnsDataRow.getCell(i);
            if(currCell == null) {
                currCell = ctnsDataRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,1,1,1).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 只有左边框
            // 设置 CTNS 公式
            if(i==2) {
                currCell.setCellFormula(ttlCtnsFormula);
                writer1Eval.evaluateFormulaCell(currCell);
            }
        }
        writer1CurrIndex++;

        //（3）输出GW
        writer1.merge(writer1CurrIndex,writer1CurrIndex,0,1,"TOTAL G.W.(KGS):",false);
        List<Object> gwData = CollUtil.newArrayList("TOTAL G.W.(KGS):",null,null,null,null,null,null,null,null);
        writer1.passCurrentRow();
        Row gwDataRow = writer1.getSheet().getRow(writer1CurrIndex);

        for(int i=0;i<gwData.size();i++) {
            Cell currCell  = gwDataRow.getCell(i);
            if(currCell == null) {
                currCell = gwDataRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,1,1,1).setDataFormat(2).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 只有左边框

        }
        writer1CurrIndex++;
        /**
         * 多加一个空行
         */
        /*writerBlankRow(writer1,blankRowData,writer1CurrIndex,"0");
        writer1CurrIndex++;*/

    }

    /**
     * 输出模板2，生成的 总金额与税收
     * 税收比例为： 0.0025
     * @param collectAmountFormula
     */
    private void writeAmountAndInsuranceForOutput1(String collectAmountFormula) {
        writer1.merge(writer1CurrIndex,writer1CurrIndex,0,7,"TOTAL INVOICE FOB USD ",false);
        List<Object> totalFobUsdData = CollUtil.newArrayList("TOTAL INVOICE FOB USD ",null,null,null,null,null,null,null,"");
        writer1.passCurrentRow();
        // 定义字体
        Font font = new FontBuilder(writer1).setFontName("Arial").setFontHeight(10).setBold(true).build();
        // 设置样式
        Row totalFobUsdDataRow = writer1.getSheet().getRow(writer1CurrIndex);
        totalFobUsdDataRow.setHeightInPoints(20);
        for(int i = 0; i < totalFobUsdData.size(); i++) {
            Cell currCell = totalFobUsdDataRow.getCell(i);
            if(currCell==null) {
                currCell = totalFobUsdDataRow.createCell(i);
            }
            if(i==0) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,0,1,1).setHorizontalAlignment(HorizontalAlignment.RIGHT).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 只有左边框
            } else if(i == totalFobUsdData.size()-2) {      // 倒数第二列，即是TOTAL INVOICE FOB USD 这一列
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,1,1,1).setHorizontalAlignment(HorizontalAlignment.RIGHT).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 只有左边框
            } else if(i == totalFobUsdData.size()-1) {      // 最后一列时，
                currCell.setCellFormula(collectAmountFormula);     // 设置公式
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(0,0,1,0).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setDataFormat2().setFont(font).build());    // 上左下边框
                writer1Eval.evaluateFormulaCell(currCell);   // 强制计算
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,1,1,1).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setDataFormat2().setFont(font).build());    // 上左下边框
            }
        }

        writer1CurrIndex++;

        writer1.merge(writer1CurrIndex,writer1CurrIndex,0,7,"Insurance:",false);
        List<Object> insuranceData = CollUtil.newArrayList("Insurance:",null,null,null,null,null,null,null,"");
        //writer1.writeRow(insuranceData);
        writer1.passCurrentRow();
        // 设置样式
        Row insuranceDataRow = writer1.getSheet().getRow(writer1CurrIndex);
        insuranceDataRow.setHeightInPoints(20);
        for(int i = 0; i < insuranceData.size(); i++) {
            Cell currCell = insuranceDataRow.getCell(i);
            if(currCell==null) {
                currCell = insuranceDataRow.createCell(i);
            }
            if(i==0) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,0,1,1).setHorizontalAlignment(HorizontalAlignment.RIGHT).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 只有左边框
            } else if(i == totalFobUsdData.size()-2) {      // 倒数第二列，即是insurance 这一列
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,1,1,1).setHorizontalAlignment(HorizontalAlignment.RIGHT).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 只有左边框
            } else if(i == totalFobUsdData.size()-1) {      // 最后一列时，
                currCell.setCellFormula("I" + writer1CurrIndex + "*0.0025");     // 设置公式,税收的计算
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(0,0,1,0).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setDataFormat2().setFont(font).build());    // 上左下边框
                writer1Eval.evaluateFormulaCell(currCell);
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,1,1,1).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setDataFormat2().setFont(font).build());    // 上左下边框
            }
        }
        writer1CurrIndex++;


        writer1.merge(writer1CurrIndex,writer1CurrIndex,0,7,"TOTAL INVOICE FOB +INS  USD ",false);
        List<Object> totalAndInsuranceData = CollUtil.newArrayList("TOTAL INVOICE FOB +INS  USD ",null,null,null,null,null,null,null,"");
        //writer1.writeRow(totalAndInsuranceData);
        writer1.passCurrentRow();
        // 设置样式
        Row totalAndInsuranceDataRow = writer1.getSheet().getRow(writer1CurrIndex);
        totalAndInsuranceDataRow.setHeightInPoints(20);
        for(int i = 0; i < totalAndInsuranceData.size(); i++) {
            Cell currCell = totalAndInsuranceDataRow.getCell(i);
            if(currCell==null) {
                currCell = totalAndInsuranceDataRow.createCell(i);
            }
            if(i==0) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,0,0,1).setHorizontalAlignment(HorizontalAlignment.RIGHT).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 只有左边框
            } else if(i == totalFobUsdData.size()-2) {      // 倒数第二列，即是insurance 这一列
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,1,0,1).setHorizontalAlignment(HorizontalAlignment.RIGHT).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 只有左边框
            } else if(i == totalFobUsdData.size()-1) {      // 最后一列时，
                currCell.setCellFormula("I" + (writer1CurrIndex-1) + " + I" + (writer1CurrIndex));     // 设置公式 + 税收的计算
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(0,0,0,0).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setDataFormat2().setFont(font).build());    // 上左下边框
                writer1Eval.evaluateFormulaCell(currCell);
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(1,1,0,1).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setDataFormat2().setFont(font).build());    // 上左下边框
            }
        }
        writer1CurrIndex++;

    }

    /**
     * 写入output1 的汇总情况
     * @param writer1
     * @param type
     * @param ttlQtyFormula
     * @param ttlAmountFormula
     */
    private void writeTotalForOutput1(ExcelWriter writer1, String type, String ttlQtyFormula, String ttlAmountFormula) {

        writer1.merge(writer1CurrIndex,writer1CurrIndex,0,5,"TOTAL " + type, false);

        List<Object> totalRowData = CollUtil.newArrayList("TOTAL " + type,"","","","","","","","");
        writer1.writeRow(totalRowData);

        Row totalRow = writer1.getSheet().getRow(writer1CurrIndex);
        totalRow.setHeightInPoints(25);

        Font font = new FontBuilder(writer1).setFontName("Arial").setFontHeight(10).setBold(true).build();
        for (int i = 0; i<totalRowData.size(); i++) {
            Cell currCell = totalRow.getCell(i);
            if(currCell==null) {
                currCell = totalRow.createCell(i);
            }
            if(i==totalRowData.size()-1) {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(0,0,0,0).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setDataFormat2().setFont(font).build());    // 上左下边框
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setCellBorder(0,0,0,0).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 上左下边框
            }

            // 设置公式：
            if(i == 6) {
                currCell.setCellFormula(ttlQtyFormula);
                writer1Eval.evaluateFormulaCell(currCell);
            } else if(i == 8) {
                currCell.setCellFormula(ttlAmountFormula);
                writer1Eval.evaluateFormulaCell(currCell);
            }
        }

    }

    private void writeRowDataForOutput1(List<Object> rowData, int currIndex) {

        writer1.writeRow(rowData);      // 写入数据行
        // 设置格式
        Row currRow = writer1.getSheet().getRow(currIndex);
        currRow.setHeightInPoints(30);
        Font font = new FontBuilder(writer1).setFontName("Arial").setFontHeight(9).build();
        for(int i=0; i< rowData.size(); i++) {
            Cell currCell = currRow.getCell(i);     // 取出当前单元格式
            if(currCell==null) {
                currCell = currRow.createCell(i);
            }
            if(i==0) {    // 第一列时
                currCell.setCellStyle(new CellStyleBuilder(writer1).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setFont(font).setCellBorder(0,0,0,0).build());
            } else if (i == rowData.size() -2) {  // 倒数第二列时，单价，需要加入$符号
                currCell.setCellStyle(new CellStyleBuilder(writer1).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setFont(font).setDataFormat2().setCellBorder(0,0,0,0).build());
            } else if(i == rowData.size() -1) {  // 最后一列时
                currCell.setCellFormula(String.valueOf(rowData.get(i)));
                currCell.setCellStyle(new CellStyleBuilder(writer1).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setFont(font).setDataFormat2().setCellBorder(0,0,0,0).build());
                writer1Eval.evaluateFormulaCell(currCell);
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer1).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setFont(font).setCellBorder(0,0,0,0).build());
            }
        }

    }

    /**
     * 输出生成2
     * @param materialList
     * @param headInfoVO
     */
    private void writeMaterialListForOutput2(List<List<Object>> materialList, HeadInfoVO headInfoVO) {

        /**
         * 如果物料列表为空，则不再写入，直接返回
         */
        if(CollectionUtil.isEmpty(materialList)) {
            return;
        }

        writer2.passRows(writer2CurrIndex);     // 先跳过一定的行，再进行写入



        int startIndex = writer2CurrIndex;      // 开始的索引

        /**
         * 遍历输出产品列表
         */
        for (List<Object> objects : materialList) {

            String no = ObjectUtil.isNotEmpty(objects.get(0))?String.valueOf(objects.get(0)):null;                // 序号
            String itemNo = ObjectUtil.isNotEmpty(objects.get(1))?String.valueOf(objects.get(1)):null;            // itemNo
            String description = ObjectUtil.isNotEmpty(objects.get(2))?String.valueOf(objects.get(2)):null;            // 描述，可能带逗号，我们只需要取逗号前的内容
            if(StrUtil.isNotBlank(description)) {
                description = description.split("[,|，]")[0];    // 截取逗号前的内容
            }
            String color = ObjectUtil.isNotEmpty(objects.get(3))?String.valueOf(objects.get(3)):null;            // 颜色
            String material = ObjectUtil.isNotEmpty(objects.get(4))?String.valueOf(objects.get(4)):null;      // material
            String itemSize = ObjectUtil.isNotEmpty(objects.get(5))?String.valueOf(objects.get(5)):null;      // itemSize
            int totalQty = 0;    // 总数量
            if(ObjectUtil.isNotEmpty(objects.get(6))) {
                try {
                    totalQty = Integer.valueOf(String.valueOf(objects.get(6)));
                } catch (Exception e) {
                    //e.printStackTrace();
                }
            }
            double unitPrice = 0;
            if(ObjectUtil.isNotEmpty(objects.get(7))) {
                try {
                    unitPrice = Double.valueOf(String.valueOf(objects.get(7)));     // 单价
                } catch (Exception e) {
                    //e.printStackTrace();
                }
            }

            String amountFormula = "G" + (writer1CurrIndex+1) + "*H" + (writer1CurrIndex+1) ;     // 总价的公式

            List<Object> rowData = CollUtil.newArrayList(no, itemNo, description, color,material,itemSize,totalQty,totalQty,null,null,null);
            writeRowDataForOutput2(rowData,writer2CurrIndex);
            writer2CurrIndex++;

        }

        int endIndex = writer2CurrIndex;      // 开始的索引

        String ttyQtyFormula = "SUM(G" + (startIndex+1) + ":G" + endIndex + ")";
        String ttlCtnsFormula = "SUM(H" + (startIndex+1) + ":H" + endIndex + ")";
        String ttlCbmFormula = "SUM(I" + (startIndex+1) + ":I" + endIndex + ")";
        String ttlNwFormula = "SUM(J" + (startIndex+1) + ":J" + endIndex + ")";
        String ttlGwFormula = "SUM(K" + (startIndex+1) + ":K" + endIndex + ")";

        /**
         * 输出汇总信息
         */
        writeTotalForOutput2(writer2,headInfoVO.getType(),ttyQtyFormula,ttlCtnsFormula,ttlCbmFormula,ttlNwFormula,ttlGwFormula);
        writer2CurrIndex++;

        /**
         * 从这里开始，输出底部的信息
         * 底部也分为两部分
         */
        // (1) 输出 总量、总重量信息
        String ttlCtnsFormulaForBottom = "H" + (endIndex + 1);
        writeTtlCbmForOutput2(ttlCtnsFormulaForBottom);

        // (2) 输出签名信息
        writeSignatureForOutput2();
    }

    /**
     * 输出签名信息
     */
    private void writeSignatureForOutput2() {

        /**
         * 跳两行空行
         */
        writer2.passCurrentRow();
        writer2CurrIndex++;

        writer2.merge(writer2CurrIndex,writer2CurrIndex,0,8,"For and on behalf of",false);
        List<String> beHalfRowData = CollUtil.newArrayList("For and on behalf of","","","","","","","","");
        writer2.passCurrentRow();

        Row beHalfRowDataRow = writer2.getSheet().getRow(writer2CurrIndex);
        Font beHalfFont = new FontBuilder(writer2).setFontName("Arial").setFontHeight(9).setBold(false).build();
        for(int i=0;i<beHalfRowData.size();i++) {
            Cell currCell  = beHalfRowDataRow.getCell(i);
            if(currCell == null) {
                currCell = beHalfRowDataRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(1,1,1,1).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setDataFormat(2).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(beHalfFont).build());    // 只有左边框
        }
        writer2CurrIndex++;


        writer2.merge(writer2CurrIndex,writer2CurrIndex,0,8,"PENTA ASIA LIMITED",false);
        List<String> limitedRowData = CollUtil.newArrayList("PENTA ASIA LIMITED","","","","","","","","");
        writer2.passCurrentRow();
        //writer1.writeRow(limitedRowData);

        Row limitedRowDataRow = writer2.getSheet().getRow(writer2CurrIndex);
        Font limitedFont = new FontBuilder(writer2).setFontName("Arial").setFontHeight(18).setItalic(true).setBold(true).build();
        for(int i=0;i<limitedRowData.size();i++) {
            Cell currCell  = limitedRowDataRow.getCell(i);
            if(currCell == null) {
                currCell = limitedRowDataRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(1,1,1,1).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setDataFormat(2).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(limitedFont).build());    // 只有左边框
        }
        writer2CurrIndex++;

        // 下边框为中边框，
        writer2.merge(writer2CurrIndex,writer2CurrIndex,0,2,"",false);
        writer2.passCurrentRow();
        Row underLineRow = writer2.getSheet().getRow(writer2CurrIndex);
        if(underLineRow==null) {
            underLineRow = writer2.getSheet().createRow(writer2CurrIndex);
        }
        underLineRow.setHeightInPoints(10);
        for(int i=0;i<3;i++) {
            Cell currCell = underLineRow.getCell(i);
            if(currCell==null) {
                currCell = underLineRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorderForModule2Limited().setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setDataFormat(2).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(limitedFont).build());    // 只有左边框

        }
        writer2CurrIndex++;


        writer2.merge(writer2CurrIndex,writer2CurrIndex,0,8,"Authorized Signature(s)",false);
        writer2.passCurrentRow();

        Row signatureRow = writer2.getSheet().getRow(writer2CurrIndex);
        for(int i=0;i<beHalfRowData.size();i++) {
            Cell currCell  = signatureRow.getCell(i);
            if(currCell == null) {
                currCell = signatureRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(1,1,1,1).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setDataFormat(2).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(beHalfFont).build());    // 只有左边框
        }
        writer2CurrIndex++;

    }


    private void writeTtlCbmForOutput2(String ttlCtnsFormula) {
        /**
         * 先输出两行空行
         */
        writer2.passCurrentRow();
        writer2CurrIndex++;

        writer2.passCurrentRow();
        writer2CurrIndex++;

        // 定义字体
        Font font = new FontBuilder(writer2).setFontName("Arial").setFontHeight(9).setBold(true).build();
        /**
         * 分别输出CBM、CTNS、GW
         */
        //（1）输出CBM
        writer2.merge(writer2CurrIndex,writer2CurrIndex,0,1,"TOTAL CBM:",false);
        List<Object> cbmData = CollUtil.newArrayList("TOTAL CBM:",null,null,null,null,null,null,null,null);
        writer2.passCurrentRow();
        Row cbmDataRow = writer2.getSheet().getRow(writer2CurrIndex);

        for(int i=0;i<cbmData.size();i++) {
            Cell currCell  = cbmDataRow.getCell(i);
            if(currCell == null) {
                currCell = cbmDataRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(1,1,1,1).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setDataFormat(2).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 只有左边框

        }
        writer2CurrIndex++;

        //(2)，输出CTNS
        writer2.merge(writer2CurrIndex,writer2CurrIndex,0,1,"TOTAL CTNS:",false);
        List<Object> ctnsData = CollUtil.newArrayList("TOTAL CTNS:",null,null,null,null,null,null,null,null);
        writer2.passCurrentRow();
        Row ctnsDataRow = writer2.getSheet().getRow(writer2CurrIndex);

        for(int i=0;i<ctnsData.size();i++) {
            Cell currCell  = ctnsDataRow.getCell(i);
            if(currCell == null) {
                currCell = ctnsDataRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(1,1,1,1).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 只有左边框
            // 设置 CTNS 公式
            if(i==2) {
                currCell.setCellFormula(ttlCtnsFormula);
                writer2Eval.evaluateFormulaCell(currCell);
            }
        }
        writer2CurrIndex++;

        //（3）输出GW
        writer2.merge(writer2CurrIndex,writer2CurrIndex,0,1,"TOTAL G.W.(KGS):",false);
        List<Object> gwData = CollUtil.newArrayList("TOTAL G.W.(KGS):",null,null,null,null,null,null,null,null);
        writer2.passCurrentRow();
        Row gwDataRow = writer2.getSheet().getRow(writer2CurrIndex);

        for(int i=0;i<gwData.size();i++) {
            Cell currCell  = gwDataRow.getCell(i);
            if(currCell == null) {
                currCell = gwDataRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(1,1,1,1).setDataFormat(2).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 只有左边框

        }
        writer2CurrIndex++;

        /*writerBlankRow(writer2,blankRowData,writer2CurrIndex,"0");
        writer2CurrIndex++;*/

    }


    /**
     *  写入汇总为了 output2
     * @param writer2
     * @param type
     * @param ttyQtyFormula
     * @param ttlCtnsFormula
     * @param ttlCbmFormula
     * @param ttlNwFormula
     * @param ttlGwFormula
     */
    private void writeTotalForOutput2(ExcelWriter writer2, String type, String ttyQtyFormula, String ttlCtnsFormula, String ttlCbmFormula, String ttlNwFormula, String ttlGwFormula) {

        writer2.merge(writer2CurrIndex,writer2CurrIndex,0,5,"TOTAL " + type, false);

        List<Object> totalRowData = CollUtil.newArrayList("TOTAL " + type,"","","","","","","","","","");
        writer2.writeRow(totalRowData);

        Row totalRow = writer2.getSheet().getRow(writer2CurrIndex);
        totalRow.setHeightInPoints(25);

        Font font = new FontBuilder(writer2).setFontHeight(9).setBold(true).build();
        for (int i = 0; i<totalRowData.size(); i++) {
            Cell currCell = totalRow.getCell(i);
            if(currCell==null) {
                currCell = totalRow.createCell(i);
            }
            currCell.setCellStyle(new CellStyleBuilder(writer2).setCellBorder(0,0,0,0).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFillBackgroundColor(1).setFillForegroundColor(1).setFont(font).build());    // 上左下边框

            // 设置公式：
            if(i == 6) {
                currCell.setCellFormula(ttyQtyFormula);
                writer2Eval.evaluateFormulaCell(currCell);
            } else if(i == 7) {
                currCell.setCellFormula(ttlCtnsFormula);
                writer2Eval.evaluateFormulaCell(currCell);
            } else if(i == 8) {
                currCell.setCellFormula(ttlCbmFormula);
                writer2Eval.evaluateFormulaCell(currCell);
            } else if(i == 9) {
                currCell.setCellFormula(ttlNwFormula);
                writer2Eval.evaluateFormulaCell(currCell);
            } else if(i == 10) {
                currCell.setCellFormula(ttlGwFormula);
                writer2Eval.evaluateFormulaCell(currCell);
            }
        }


    }


    /**
     * 写入
     * @param rowData
     * @param currIndex
     */
    private void writeRowDataForOutput2(List<Object> rowData, int currIndex) {
        writer2.writeRow(rowData);      // 写入数据行
        // 设置格式
        Row currRow = writer2.getSheet().getRow(currIndex);
        currRow.setHeightInPoints(30);
        Font font = new FontBuilder(writer2).setFontName("Arial").setFontHeight(9).build();
        for(int i=0; i< rowData.size(); i++) {
            Cell currCell = currRow.getCell(i);     // 取出当前单元格式
            if(i==0) {    // 第一列时
                currCell.setCellStyle(new CellStyleBuilder(writer2).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setFont(font).setCellBorder(0,0,0,0).build());
            }else if (i == rowData.size() -3) {  // 倒数第二列时，单价，需要加入$符号
                currCell.setCellStyle(new CellStyleBuilder(writer2).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setFont(font).setDataFormat(3).setCellBorder(0,0,0,0).build());
            } else if (i == rowData.size() -2) {  // 倒数第二列时，单价，需要加入$符号
                currCell.setCellStyle(new CellStyleBuilder(writer2).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setFont(font).setDataFormat().setCellBorder(0,0,0,0).build());
            } else if(i == rowData.size() -1) {  // 最后一列时
                currCell.setCellStyle(new CellStyleBuilder(writer2).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setFont(font).setDataFormat().setCellBorder(0,0,0,0).build());
            } else {
                currCell.setCellStyle(new CellStyleBuilder(writer2).setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setWrapText(true).setFont(font).setCellBorder(0,0,0,0).build());
            }
        }

    }


    /**
     * 取出物品列表，模板2，
     * @param dataList
     * @return
     */
    public static List<List<Object>> getMaterialListFromDataList(List<List<Object>> dataList) {

        List<List<Object>> materialList = new ArrayList<>();           // 物料列表

        boolean orgListStatus = false;    // 是否正在组织列表的状态，false 表示没有在组织
        for (int i = 0; i < dataList.size(); i++) {
            List<Object> objects = dataList.get(i);    // 取出一条数据
            String column0Value = ObjectUtil.isNotEmpty(objects.get(0))?String.valueOf(objects.get(0)):null;         // 查看当前第0列的数据
            if("1".equals(column0Value)) {
                orgListStatus = true;            // 表示正在构建列表中

                materialList.add(objects);

            } else if(orgListStatus && StrUtil.isNumeric(column0Value)) {      // 如果也数字，表示是同一组的
                materialList.add(objects);
            }

            if(orgListStatus && StrUtil.isNotBlank(column0Value) && StrUtil.containsAny(column0Value,"TOTAL")) {     // 如果在组织列表时，且第0行数据增加

                orgListStatus = false;

            }

        }

        return materialList;
    }


    /**
     * 写入表头
     *
     * @param headInfoVO
     */
    private void outputHead(HeadInfoVO headInfoVO) {

        if(ObjectUtil.isEmpty(headInfoVO)) {
            return;
        }

        /**
         * 一、输出生成1的表头
         */
        Font telAndAddressFont = new FontBuilder(writer1).setFontHeight(8).setFontName("Arial").build();
        writeContent(writer1,1,8,headInfoVO.getTelInfo(),new CellStyleBuilder(writer1).setHorizontalAlignment(HorizontalAlignment.RIGHT).setFillBackgroundColor(1).setFillForegroundColor(1).setCellBorder(1,1,1,0).setFont(telAndAddressFont).build());
        writeContent(writer1,2,8,headInfoVO.getAddress(),new CellStyleBuilder(writer1).setHorizontalAlignment(HorizontalAlignment.RIGHT).setFillBackgroundColor(1).setFillForegroundColor(1).setCellBorderForModule2Address().setFont(telAndAddressFont).build());

        writeContent(writer1,6,8, headInfoVO.getDate(),new CellStyleBuilder(writer1).setHorizontalAlignment(HorizontalAlignment.RIGHT).setFillBackgroundColor(1).setFillForegroundColor(1).setCellBorder(1,1,1,0).setFont(telAndAddressFont).build());
        writeContent(writer1,7,8, headInfoVO.getInvNo(),new CellStyleBuilder(writer1).setHorizontalAlignment(HorizontalAlignment.RIGHT).setFillBackgroundColor(1).setFillForegroundColor(1).setCellBorder(1,1,1,0).setFont(telAndAddressFont).build());

        Font invoiceFont = new FontBuilder(writer1).setFontHeight(9).setFontName("Arial").build();
        writeContent(writer1,8,0,"Invoice to :" + headInfoVO.getProformaInvoiceTo(),new CellStyleBuilder(writer1).setVerticalAlignment(VerticalAlignment.TOP).setWrapText(true).setFont(invoiceFont).setCellBorder(0,0,0,0).build());
        writeContent(writer1,8,4,headInfoVO.getPartLoading(),new CellStyleBuilder(writer1).setVerticalAlignment(VerticalAlignment.TOP).setFont(invoiceFont).setWrapText(true).setCellBorder(0,0,0,0).build());

        // 写入运输港口
        writeContent(writer1,10,7,headInfoVO.getTransportInfo(),new CellStyleBuilder(writer1).setCellBorder("3").setHorizontalAlignment(HorizontalAlignment.CENTER).setVerticalAlignment(VerticalAlignment.CENTER).setFont(new FontBuilder(writer1).setFontHeight(10).setFontName("Arial").setBold(true).build()).build());

        // 填写集装箱信息
        for (int i=0;i < headInfoVO.getContainerCount();i++) {
            if(headInfoVO.getContainerCount()==1) {    // 只有一个集装箱的时候，不加编号
                writer1.merge(writer1CurrIndex,writer1CurrIndex,0,8,"CONTAINER NO.&SEAL NO.: ",new CellStyleBuilder(writer1).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setCellBorder("234").setFont(new FontBuilder(writer1).setFontName("Arial").setBold(true).setFontHeight(10).build()).build());
            } else {
                writer1.merge(writer1CurrIndex,writer1CurrIndex,0,8,"CONTAINER " + (i+1) + " NO.&SEAL NO.: ",new CellStyleBuilder(writer1).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setCellBorder("234").setFont(new FontBuilder(writer1).setFontName("Arial").setBold(true).setFontHeight(10).build()).build());
            }

            writer1CurrIndex++;     // 行数索引增加1
        }

        /**
         * 二、写入生成2的表头
         */
        writeContent(writer2,1,10,headInfoVO.getTelInfo(),new CellStyleBuilder(writer2).setHorizontalAlignment(HorizontalAlignment.RIGHT).setFillBackgroundColor(1).setFillForegroundColor(1).setCellBorder(1,1,1,0).setFont(telAndAddressFont).build());
        writeContent(writer2,2,10,headInfoVO.getAddress(),new CellStyleBuilder(writer2).setHorizontalAlignment(HorizontalAlignment.RIGHT).setFillBackgroundColor(1).setFillForegroundColor(1).setCellBorderForModule2Address().setFont(telAndAddressFont).build());

        writeContent(writer2,6,10, headInfoVO.getDate(),new CellStyleBuilder(writer2).setHorizontalAlignment(HorizontalAlignment.RIGHT).setFillBackgroundColor(1).setFillForegroundColor(1).setCellBorder(1,1,1,0).setFont(telAndAddressFont).build());
        writeContent(writer2,7,10, headInfoVO.getInvNo(),new CellStyleBuilder(writer2).setHorizontalAlignment(HorizontalAlignment.RIGHT).setFillBackgroundColor(1).setFillForegroundColor(1).setCellBorder(1,1,1,0).setFont(telAndAddressFont).build());

        Font invoiceFont2 = new FontBuilder(writer2).setFontHeight(9).setFontName("Arial").build();
        writeContent(writer2,8,0,"PKL to :" + headInfoVO.getProformaInvoiceTo(),new CellStyleBuilder(writer2).setVerticalAlignment(VerticalAlignment.TOP).setWrapText(true).setFont(invoiceFont2).setCellBorder(0,0,0,0).build());
        writeContent(writer2,8,5,headInfoVO.getPartLoading(),new CellStyleBuilder(writer2).setVerticalAlignment(VerticalAlignment.TOP).setFont(invoiceFont2).setWrapText(true).setCellBorder(0,0,0,0).build());

        // 填写集装箱信息
        for (int i=0;i < headInfoVO.getContainerCount();i++) {
            if(headInfoVO.getContainerCount()==1) {    // 只有一个集装箱的时候，不加编号
                writer2.merge(writer2CurrIndex,writer2CurrIndex,0,10,"CONTAINER NO.&SEAL NO.: ",new CellStyleBuilder(writer2).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setCellBorder("234").setFont(new FontBuilder(writer2).setFontName("Arial").setBold(true).setFontHeight(10).build()).build());
            } else {
                writer2.merge(writer2CurrIndex,writer2CurrIndex,0,10,"CONTAINER " + (i + 1) + " NO.&SEAL NO.: ",new CellStyleBuilder(writer2).setHorizontalAlignment(HorizontalAlignment.LEFT).setVerticalAlignment(VerticalAlignment.CENTER).setCellBorder("234").setFont(new FontBuilder(writer2).setFontName("Arial").setBold(true).setFontHeight(10).build()).build());
            }
            writer2CurrIndex++;     // 行数索引增加1
        }

    }


    @Override
    public HeadInfoVO getHeadInfoByDataList() {
        HeadInfoVO headInfoVO = null;
        if(CollectionUtil.isNotEmpty(dataList)) {
            try {
                String telInfo = getContentFromDataList(dataList,1,8);
                String address = getContentFromDataList(dataList,2,8);
                String date = getContentFromDataList(dataList,4,8);

                String piNo = getContentFromDataList(dataList,5,8);
                if(StrUtil.isNotBlank(piNo)) {
                    String[] piNoSplit = piNo.split("[:|：]");
                    if(piNoSplit.length>1) {
                        piNo = "INV No.: " + piNoSplit[1];     // 重新拼接
                    }
                }

                /**
                 * 形式发票收款人：
                 *      "Proforma Invoice to :
                 *          ORCA DECO GAB
                 *          LIKOUALA  SAINT GERMAIN( EN FACE DE KAMELIE)
                 *          BP 1540 / LIBREVILLE GABON
                 *          TELEPHONE : +24102764444 "
                 */
                String proforma = getContentFromDataList(dataList, 6, 0);
                if(StrUtil.isNotBlank(proforma)) {
                    proforma = proforma.replace("Proforma Invoice to :","");
                }

                /**
                 * 装运港信息
                 *          "Port of Loading: Shanghai China
                 *          Ship to: GABON
                 *
                 *          Transport:   1*40HQ by sea shipment
                 * "
                 */
                String partLoading = getContentFromDataList(dataList,6,8);

                /**
                 * 得到的map有两个结果：
                 *      port -> Shanghai China
                 *      type -> 1*40HQ
                 */
                Map<String,String> portAndTypeMap = getPortAndTypeMapByPartLoading(partLoading);
                String port = portAndTypeMap.get("port");
                String type = portAndTypeMap.get("type");
                String shipTo = portAndTypeMap.get("shipTo");    // 出港口

                /**
                 * 根据 type 的值，解析到底有多少个集装箱
                 * 1*40HQ 或是 X
                 */
                int containerCount = 0;
                if(StrUtil.isNotBlank(type)) {
                    try {
                        containerCount = Integer.valueOf(type.split("[*|X|x]")[0]);
                    } catch (Exception e) {
                        //e.printStackTrace();
                    }
                }

                /**
                 * 读取运输港口, 用于在 Unit Price / Amount 下使用
                 */
                String transportInfo = getContentFromDataList(dataList,8,8);

                String supplier = getSupplierFromDataList(dataList);

                /**
                 * 实例化
                 */
                headInfoVO = new HeadInfoVO();

                headInfoVO.setTelInfo(telInfo);
                headInfoVO.setAddress(address);
                headInfoVO.setSupplier(supplier);
                headInfoVO.setDate(date);
                headInfoVO.setPort(port);
                headInfoVO.setType(type);
                headInfoVO.setContainerCount(containerCount);
                headInfoVO.setProformaInvoiceTo(proforma);
                headInfoVO.setTransportInfo(transportInfo);
                headInfoVO.setInvNo(piNo);
                headInfoVO.setShipTo(shipTo);   // 出港口
                headInfoVO.setPartLoading(partLoading);

            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return headInfoVO;
    }

}
